In typical database systems, end users store, update and retrieve information by submitting commands to a database application. For instance, the commands may be submitted in the ubiquitous structured query language (SQL). Unfortunately, most if not all database systems are susceptible to hardware or system failures that can result in a loss of data or a corruption of data. Moreover, it is well known that user errors or application errors create more database logical errors than do hardware and system failures. Unlike physical errors created by hardware or system failures, logical errors created by users and/or applications are more difficult to detect, harder to identify in scope, and generally require more time and resources to resolve.
Most database vendors provide methods to recover a database when a hardware failure or system failure occurs. That is, the entire database can be recovered to a state that existed at a specified time in the past. However, most database vendors do not provide a method of directly rollbacking a selected portion of the database. In most cases, after identifying the errors and the scope of the errors, a database administrator has to manually rollback a selected portion of data to a specified time in a primary database with the help of a secondary database built to the specified time. The second database will then use a transaction log from the primary database to move forward to the specified time. The set up time for a secondary database is very time consuming, and for each specified time, one needs to set up a secondary database. Furthermore, if the database administrator decides to move back farther in time, then the database administrator must set up another secondary database from the backup copy and move forward to the new specified time. After the selective rollback, the database administrator must also perform additional administrative tasks to bring the application data to a logical consistent state.
Oracle provides a method of querying data at a point-in-time (Flashback Query, Oracle9i Application Developer's Guide—Fundamentals, p. 7-43). The Flashback Query allows the selective rollback of a portion of a database with some limitations. For instance, one limitation is that the undo space of the database needs to be managed by an automatic undo management to use this feature effectively. The undo segment consumes a lot more disk space than the related data rows do in their related tablespaces. Thus, the availability of the storage capacity will limit how far back in the time the Flashback can query the database. Another limitation is that the specified period of time (i.e., undo retention period) in which the Flashback can query back into the past is the same for all of the tables. Specifically, Oracle uses a single initialization parameter (i.e., UNDO_RETENTION) to control how far into the past a user may query the database. Moreover, the user does not have a choice of assigning a different undo retention period for each different table. Further, unimportant tables may use a lot more of the undo segment space than do the more important tables. When the undo space is full, some unexpired undo spaces will be reused such that the data related to the reused undo space can not be flashbacked to the retention time.
An additional limitation that exists with the prior art system is that a database administrator is not able to obtain a precise flashback. Essentially, the time specified in DBMS_FLASHBACK.ENABLE_AT_TIME statement or in an AS OF TIMESTAMP clause is mapped to a system commit number (SCN), which serves as the basis for flashback queries. Thus, flashback query results are based on the SCN. The database tracks and records the SCN at five minute intervals after database startup, and logs the information for the five most recent days of operation. Any timestamp used in the Flashback query may be rounded down by up to five minutes. The Flashback is not available for a newly created table for up to five minutes. Further, since the database logs the information for only the five most recent days of operation, the user needs to keep track of the SCNs (or otherwise determine the SCNs) if the user wants to flashback by more than five days. Despite doing so, the undo data may be overwritten, in which case the Flashback query will result in an error.
Another limitation is that some structure changes of a table, like the drop/modify column of the table will invalidate the undo data. Therefore, the user cannot query data earlier than the time the table was changed. Still another limitation is that the user cannot Flashback a remote table through a database link. An additional limitation is that the performance of a flashback query depends on how much data needs to be recreated, which is an expensive process. Another limitation is that the change history of a row is not easy to obtain through the Flashback query. That is, the Flashback provides one view at a specific time, as opposed to a continuous view.
Thus, it would be advantageous to quickly and efficiently identify the scope and timeframe of a database application error in order to reduce the mean time to repair and the database downtime. Furthermore, it would be desirable to provide online history images, create online point-in-time views of application tables, reconstruct the equivalent SQL statements of a committed transaction, provide a selective audit trail report on demand, and permit selective online rollbacks a single row, a single transaction, a user session, or all user tables in an application database.